import java.sql.*;
import java.util.ArrayList;
import java.util.List;

public class StudentManager {
    private DatabaseConnection dbConnection;

    public StudentManager() {
        this.dbConnection = new DatabaseConnection();
    }

    // 添加学生信息到数据库
    public void addStudent(Student student) {
        String sql = "INSERT INTO students (id, name, gender, class, math_score, java_score) VALUES (?, ?, ?, ?, ?, ?)";
        try (Connection conn = dbConnection.getConnection();
             PreparedStatement pstmt = conn.prepareStatement(sql)) {
            pstmt.setInt(1, student.getId());
            pstmt.setString(2, student.getName());
            pstmt.setString(3, student.getGender());
            pstmt.setString(4, student.getClassName());
            pstmt.setDouble(5, student.getMathScore());
            pstmt.setDouble(6, student.getJavaScore());
            pstmt.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    // 根据ID查询学生信息
    public Student getStudentById(int id) {
        String sql = "SELECT * FROM students WHERE id = ?";
        try (Connection conn = dbConnection.getConnection();
             PreparedStatement pstmt = conn.prepareStatement(sql)) {
            pstmt.setInt(1, id);
            ResultSet rs = pstmt.executeQuery();
            if (rs.next()) {
                Student student = new Student();
                student.setId(rs.getInt("id"));
                student.setName(rs.getString("name"));
                student.setGender(rs.getString("gender"));
                student.setClassName(rs.getString("class"));
                student.setMathScore(rs.getDouble("math_score"));
                student.setJavaScore(rs.getDouble("java_score"));
                return student;
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return null;
    }

    // 显示所有学生信息
    public List<Student> getAllStudents() {
        List<Student> students = new ArrayList<>();
        String sql = "SELECT * FROM students";
        try (Connection conn = dbConnection.getConnection();
             Statement stmt = conn.createStatement();
             ResultSet rs = stmt.executeQuery(sql)) {
            while (rs.next()) {
                Student student = new Student();
                student.setId(rs.getInt("id"));
                student.setName(rs.getString("name"));
                student.setGender(rs.getString("gender"));
                student.setClassName(rs.getString("class"));
                student.setMathScore(rs.getDouble("math_score"));
                student.setJavaScore(rs.getDouble("java_score"));
                students.add(student);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return students;
    }

    // 计算学生各科目的平均分数
    public double[] calculateAverageScores() {
        double totalMathScore = 0;
        double totalJavaScore = 0;
        int count = 0;
        String sql = "SELECT math_score, java_score FROM students";
        try (Connection conn = dbConnection.getConnection();
             Statement stmt = conn.createStatement();
             ResultSet rs = stmt.executeQuery(sql)) {
            while (rs.next()) {
                totalMathScore += rs.getDouble("math_score");
                totalJavaScore += rs.getDouble("java_score");
                count++;
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return new double[]{count > 0 ? totalMathScore / count : 0, count > 0 ? totalJavaScore / count : 0};
    }
}